SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
if exists (select * from sysobjects where id = object_id('dbo.GetHogByHoggeryID_Proc'))
	drop PROCEDURE "dbo"."GetHogByHoggeryID_Proc"
Go
CREATE PROCEDURE GetHogByHoggeryID_Proc(
	@hoggeryId int,
	@PageIndex int = 1,            
	@PageSize int = 5
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	 DECLARE @STARTID nvarchar(50) 
    DECLARE @ENDID nvarchar(50) 
    SET @STARTID = convert(nvarchar(50),(@PageIndex - 1) * @PageSize + 1) 
    SET @ENDID = convert(nvarchar(50),@PageIndex * @PageSize) 
	select * from (
	select ROW_NUMBER() over(order by h.HogID) as RowNum,h.PigCategory,gp.PhaseName,pp.CustomizeID,h.BitID,pb.TypeName,ps.SourceType,h.IsInFarm,h.Account from Hog h
		left join GrowPhase gp on h.PhaseID=gp.PhaseID
		left join PigPen pp on pp.PigPenID=h.PigPenId
		left join PigBreed pb on pb.TypeID=h.TypeID
		left join PigSource ps on ps.SourceID=h.SourceID
		where h.HoggeryID=@hoggeryId and h.IsInFarm=1
		) as temp
		where temp.RowNum between @STARTID and @ENDID 
	
END
GO
